package cn.itcast.itcaststore.dao.impl;

import cn.itcast.itcaststore.dao.ProductDao;
import cn.itcast.itcaststore.domain.OrderItem;
import cn.itcast.itcaststore.domain.Orders;
import cn.itcast.itcaststore.domain.Products;
import cn.itcast.itcaststore.utils.DataSourceUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    private QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

    @Override
    public List<Object[]> salesList(String year, String month) throws SQLException {
        //定义sql
        String sql = "select products.name,SUM(orderitem.buynum) totalsalnum "+
                   "from orders,products,orderItem "+
                   "where orders.id=orderItem.order_id "+
                   "and products.id=orderItem.product_id "+
                   "and orders.paystate=1 and year(ordertime)=? "+
                   "and month(ordertime)=? "+
                   "group by products.name order by totalsalnum DESC";
        //执行
        return runner.query(sql,new ArrayListHandler(),year,month); }

    @Override
    public List<Products> findProductByPage(int currentPage, int currentCount, String category) {
        //创建查询sql,第一个？是分类名，第二个问号参数是当前页码，第三个？是一页的个数
        String sql = "";
        List<Products> query=null;
        if (category.equals("全部商品")){
            sql = "SELECT * FROM products LIMIT ?,?";
            try {
                query = runner.query(sql, new BeanListHandler<Products>(Products.class),  currentCount * (currentPage - 1), currentCount);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return query;
        }else {
             sql = "SELECT * FROM products WHERE category=? LIMIT ?,?";
            try {
                query = runner.query(sql, new BeanListHandler<Products>(Products.class), category, currentCount * (currentPage - 1), currentCount);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return query;
        }


    }

    @Override
    public int findProductTotalCount(String category) {
        String sql = "";
        int count = 0;
        if (category.equals("全部商品")){
            System.out.println(111);
            sql = "SELECT COUNT(*) FROM products";
            try {
                Object obj = runner.query(sql, new ScalarHandler());
                count = Integer.parseInt(String.valueOf(obj));
                return count;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return count;
        }else {
            sql = "SELECT COUNT(*) FROM products WHERE category=?";
            count = 0;
            try {
                Object obj = runner.query(sql, new ScalarHandler(), category);
                count = Integer.parseInt(String.valueOf(obj));
                return count;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return count;
        }

    }

    @Override
    public void changeProductNum(Orders order) throws SQLException {
        String sql = "update products set pnum=pnum-? where id=?";
        QueryRunner runner = new QueryRunner();
        List<OrderItem> items = order.getOrderItems();
        Object[][] params = new Object[items.size()][2];//【所有商品的数量】【2个单位长度】
        for (int i = 0; i < params.length; i++) {
            params[i][0] = items.get(i).getBuynum();
            params[i][1] = items.get(i).getP().getId();
        }
        runner.batch(DataSourceUtils.getConnection(), sql, params);//批量处理
    }


    @Override
        public List<Products> findProductByManyCondition (String id, String name, String category, String
        minprice, String maxprice) throws SQLException {
            List<Object> list = new ArrayList<Object>();
            String sql = "select * from products where 1=1 ";
            // 商品编号
            if (id != null && id.trim().length() > 0) {
                sql += " and id = ? ";
                list.add(id);
            }
            // 商品名称
            if (name != null && name.trim().length() > 0) {
                sql += " and name = ? ";
                list.add(name);
            }
            // 商品类别
            if (category != null && category.trim().length() > 0) {
                sql += " and category = ? ";
                list.add(category);
            }
            // 价格区间
            if (minprice != null && maxprice != null && minprice.trim().length() > 0 && maxprice.trim().length() > 0) {
                sql += " and price between ? and ?";
                list.add(minprice);
                list.add(maxprice);
            }
            // 将集合转化成对象数组类型
            Object[] params = list.toArray();
            return runner.query(sql, new BeanListHandler<>(Products.class), params);
        }

        @Override
        public int findTotalCountByName (String searchfield){
            //创建查询sql
            String sql = "SELECT COUNT(*) FROM products WHERE NAME LIKE CONCAT  ('%' ? '%')";
            int count = 0;
            try {
                Object obj = runner.query(sql, new ScalarHandler(), searchfield);
                count = Integer.parseInt(String.valueOf(obj));
                return count;
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return count;
        }

        @Override
        public List<Products> findProductByNameByPage ( int currentPage, int currentCount, String searchfield){
            //创建查询sql,第一个？是图书名，第二个问号参数是当前页码，第三个？是一页的个数
            String sql = "SELECT * FROM products WHERE NAME LIKE CONCAT  ('%' ? '%') LIMIT ?,?";
            List<Products> query = null;
            try {
                query = runner.query(sql, new BeanListHandler<Products>(Products.class), searchfield, currentCount * (currentPage - 1), currentCount);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return query;
        }

        @Override
        public List<Object[]> getWeekHotProduct () {
            String sql = "SELECT products.id,products.name,products.imgurl,SUM(orderitem.buynum) totalsalnum \n" +
                    "FROM orderitem,orders,products \n" +
                    "WHERE orderitem.order_id=orders.id \n" +
                    "AND products.id = orderitem.product_id \n" +
                    "AND orders.paystate=1 \n" +
                    "AND orders.ordertime>DATE_SUB(NOW(),INTERVAL 7 DAY ) \n" +
                    "GROUP BY products.id,products.name,products.imgurl \n" +
                    "ORDER BY totalsalnum DESC LIMIT 0,2";
            List<Object[]> query = null;
            try {
                query = runner.query(sql, new ArrayListHandler());
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return query;
        }

        @Override
        public List<Products> listAll () throws SQLException {
            //创建sql语句
            String sql = "select * from products";
            return runner.query(sql, new BeanListHandler<Products>(Products.class));
        }

    @Override
    public Products findProductById(String id) throws SQLException {
        String sql = "select * from products where id=?";
        return runner.query(sql, new BeanHandler<Products>(Products.class), id);
    }

    @Override
    public void addProduct(Products p) throws SQLException {
        // 创建sql语句
        String sql = "insert into products values(?,?,?,?,?,?,?)";
        runner.update(sql,p.getId(),p.getName(),p.getPrice(),p.getCategory(),p.getpNum(),p.getImgUrl(),p.getDescription());
    }

    @Override
    public void editProduct(Products p) throws SQLException {
        // 1.创建集合并将商品信息添加到集合中
        List<Object> obj = new ArrayList<>();
        obj.add(p.getName());
        obj.add(p.getPrice());
        obj.add(p.getCategory());
        obj.add(p.getpNum());
        obj.add(p.getDescription());
        // 2.创建sql 语句，并拼接sql
        String sql = "update products " + "set name =?,price = ? ,category = ?,pnum = ?,description =?";
        //判断是否有照片
        if (p.getImgUrl() != null && p.getImgUrl().trim().length() >0){
            sql += " , imgurl=?";
            obj.add(p.getImgUrl());
        }
        sql += " where id =?";
        obj.add(p.getId());
        // 3.创建QueryRunner对象
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        // 4.使用QueryRunner对象的update()方法更新数据
        runner.update(sql,obj.toArray());
    }

    @Override
    public void deleteProduct(String id) throws SQLException {
        String sql = "delete from products where id = ?";
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        runner.update(sql,id);
    }

    @Override
    public void updateProductNum(List<OrderItem> items) throws SQLException {

        String sql = "update products set pnum=pnum+? where id=?";
        QueryRunner runner = new QueryRunner();

        Object[][] params = new Object[items.size()][2];

        for (int i = 0; i < params.length; i++) {
            params[i][0] = items.get(i).getBuynum();
            params[i][1] = items.get(i).getP().getId();
        }

        runner.batch(DataSourceUtils.getConnection(), sql, params);
    }

}
